﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.Basic;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;

namespace ServiceApplication.Dao.Basic
{
    public class UserDao
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="userEntity"></param>
        /// <returns>刚插入的主键</returns>
        public int AddEntity(UserEntity userEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", userEntity.user_id));
            parameterList.Add(new SqlParameter("@user_name", userEntity.user_name));
            parameterList.Add(new SqlParameter("@staff_id", userEntity.staff_id));
            parameterList.Add(new SqlParameter("@user_ctime", userEntity.user_ctime));
            parameterList.Add(new SqlParameter("@user_loginid", userEntity.user_loginid));
            parameterList.Add(new SqlParameter("@user_psw", userEntity.user_psw));
            parameterList.Add(new SqlParameter("@user_status", userEntity.user_status));
            parameterList.Add(new SqlParameter("@user_lasttime", userEntity.user_lasttime));
            parameterList.Add(new SqlParameter("@auth_id", userEntity.auth_id));
            parameterList.Add(new SqlParameter("@spqx_id", userEntity.spqx_id));
            parameterList.Add(new SqlParameter("@user_img", userEntity.user_img));
            parameterList.Add(new SqlParameter("@user_znxdx", userEntity.user_znxdx));
            parameterList.Add(new SqlParameter("@user_rldx", userEntity.user_rldx));

            string sql = "insert into jc_user (user_name,staff_id,user_ctime,user_loginid,user_psw,user_status,user_lasttime,auth_id,spqx_id,user_img,user_znxdx,user_rldx) values " +
                " (@user_name,@staff_id,@user_ctime,@user_loginid,@user_psw,@user_status,@user_lasttime,@auth_id,@spqx_id,@user_img,@user_znxdx,@user_rldx); " +
                " select @@identity";

            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());

            return int.Parse(id);
        }

        public void AddUserIntoChatTable(String name, String password, String nickname, String email)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@name", name));
            parameterList.Add(new SqlParameter("@password", password));
            parameterList.Add(new SqlParameter("@nickname", nickname));
            parameterList.Add(new SqlParameter("@email", email));
            parameterList.Add(new SqlParameter("@inviteCode", Guid.NewGuid().ToString().ToUpper().Replace("-", "")));
            String sql = "CreateUser";

            DBExecuteUtil.executeSqlWithPar(sql, CommandType.StoredProcedure, parameterList.ToArray());
        }

        public void DleteEntity(int user_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "delete from jc_user where user_id=@user_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">唯一编码</param>
        /// <returns></returns>
        public UserEntity QueryEntity(int user_id)
        {
            UserEntity userEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "select user_id,user_name,staff_id,user_ctime,user_loginid,user_psw,user_status,u.auth_id,spqx_id,user_img,user_lasttime,authority,user_znxdx,user_rldx from jc_user u,user_auth a  " +
                             " where u.auth_id=a.auth_id and user_id=@user_id ";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null == result) return null;

            userEntity = new UserEntity();
            userEntity.user_id = int.Parse(result[0][0]);
            userEntity.user_name = result[0][1];
            userEntity.staff_id = int.Parse(result[0][2]);
            userEntity.user_ctime = result[0][3];
            userEntity.user_loginid = result[0][4];
            userEntity.user_psw = result[0][5];
            userEntity.user_status = result[0][6];
            userEntity.auth_id = int.Parse(result[0][7]);
            userEntity.spqx_id = int.Parse(result[0][8]);
            userEntity.user_img = result[0][9];
            userEntity.user_lasttime = result[0][10];
            userEntity.authority = result[0][11];
            userEntity.user_znxdx = int.Parse(result[0][12]);
            userEntity.user_rldx = int.Parse(result[0][13]);
            return userEntity;
        }

        public UserEntity QueryEntity(string user_name)
        {
            UserEntity userEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_name", user_name));
            string sql = "select user_id,user_name,staff_id,user_ctime,user_loginid,user_psw,user_status,u.auth_id,spqx_id,user_img,user_lasttime,authority,user_znxdx,user_rldx from jc_user u,user_auth a  " +
                             " where u.auth_id=a.auth_id and user_name=@user_name ";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null == result) return null;

            userEntity = new UserEntity();
            userEntity.user_id = int.Parse(result[0][0]);
            userEntity.user_name = result[0][1];
            userEntity.staff_id = int.Parse(result[0][2]);
            userEntity.user_ctime = result[0][3];
            userEntity.user_loginid = result[0][4];
            userEntity.user_psw = result[0][5];
            userEntity.user_status = result[0][6];
            userEntity.auth_id = int.Parse(result[0][7]);
            userEntity.spqx_id = int.Parse(result[0][8]);
            userEntity.user_img = result[0][9];
            userEntity.user_lasttime = result[0][10];
            userEntity.authority = result[0][11];
            userEntity.user_znxdx = int.Parse(result[0][12]);
            userEntity.user_rldx = int.Parse(result[0][13]);
            return userEntity;
        }

        public DataTable QueryBySpqx(int[] spqx_id)
        {
            string sql = "select u.user_id 用户编码,user_name 用户名称 " +
                " from jc_user u,wf_spqxdyb d where u.user_id=d.user_id and user_status=1 and d.spqx_id in (";
            foreach (var item in spqx_id)
            {
                sql += "'" + item + "',";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";
            var dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }

        public DataTable QueryBySpqxDept(int[] spqx_id, int dept_id)
        {
            string sql = "select u.user_id 用户编码,user_name 用户名称 " +
                " from jc_user  u,jc_staff s,wf_spqxdyb d where u.user_id=d.user_id and user_status=1 and u.staff_id=s.staff_id and s.dept_id='" + dept_id + "' and d.spqx_id in (";
            foreach (var item in spqx_id)
            {
                sql += "'" + item + "',";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";
            var dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }

        public DataTable QueryAllUsers()
        {
            string sql = " select user_id 用户编码,user_name 用户名称,users.staff_id 员工编码,sta.staff_name 员工名称, " +
                            " user_ctime 创建时间,user_loginid 账号,user_psw 密码, user_status 状态,user_lasttime 上次登陆时间, " +
                            " auth_id 权限编码,spqx_id 审批权限编码,user_img 头像,sta.duty_id 职位编码,duty_name 职位名称," +
                            " sta.dept_id 部门编码,dept_name 部门名称,staff_kybz 在职标志" +
                             " from jc_user users,jc_dept depts,jc_duty dutys,jc_staff sta" +
                             "  where sta.dept_id=depts.dept_id and sta.duty_id=dutys.duty_id and users.staff_id=sta.staff_id and users.user_status=1 ";

            return DBExecuteUtil.querySqlTable(sql);
        }

        public DataTable QueryUsersByCondition(UserEntity usrEnty, string stime, string etime)
        {
            List<SqlParameter> parameterList;
            String sql = proUserSql(usrEnty, stime, etime, out parameterList);
            return DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
        }

        private String proUserSql(UserEntity usrEnty, string stime, string etime, out List<SqlParameter> parameterList)
        {
            parameterList = new List<SqlParameter>();

            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT user_id 用户编码,user_name 用户名称,usr.staff_id 员工编码,user_ctime 创建时间,user_loginid 账号,user_psw 密码,user_status 状态,user_lasttime 上次登陆时间,auth_id 权限编码,spqx_id 审批权限编码,user_img 头像 ");
            sb.Append(" FROM jc_user usr,jc_dept dpt,jc_staff s  Where s.dept_id=dpt.dept_id and usr.staff_id=s.staff_id  ");
            if (!String.IsNullOrEmpty(usrEnty.user_name))
            {
                sb.Append("AND usr.user_name like '%'+@user_name+ '%'");
                parameterList.Add(new SqlParameter("@user_name", usrEnty.user_name));
            }
            if (!String.IsNullOrEmpty(stime))
            {
                sb.Append("AND user_ctime >=@stime ");
                parameterList.Add(new SqlParameter("@stime", stime));
            }
            if (!String.IsNullOrEmpty(etime))
            {
                sb.Append("AND user_ctime <=@etime ");
                parameterList.Add(new SqlParameter("@etime", etime));
            }
            if (!String.IsNullOrEmpty(usrEnty.dept_name))
            {
                sb.Append("AND dpt.dept_name like '%'+@dept_name+'%'");
                parameterList.Add(new SqlParameter("@dept_name", usrEnty.dept_name));
            }
            if (!String.IsNullOrEmpty(usrEnty.user_status))
            {
                sb.Append("AND usr.user_status =@user_status ");
                parameterList.Add(new SqlParameter("@user_status", usrEnty.user_status));
            }
            return sb.ToString();
        }

        public void UpdateHeadImg(int id, String imgPath)
        {

            String sql = "update jc_user set [user_img]=@userImg where [user_id]=@userId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userImg", imgPath));
            parameterList.Add(new SqlParameter("@userId", id));
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public String QueryUserCount()
        {
            //user_status 是表示该账户是否可用，不是在线表示   mlh   2012-10
            // 更新用户状态

            //String sql = "update jc_user set [user_status]=0 where convert(datetime,[user_lasttime])+convert(datetime, '00:10:00') < getDate()";
            //DBExecuteUtil.executeSql(sql);

            //// 获得在线用户数量
            //sql = "select count([user_id]) from jc_user where [user_status]='1'";
            //return DBExecuteUtil.getUniqueResult(sql);
            return string.Empty;
        }

        /// <summary>
        ///  更改用户部门
        /// </summary>
        /// <param name="usrIds">格式 : '1','2','3','4'</param>
        /// <param name="deptId"></param>
        public void ChangeDept(String usrIds, String deptId)
        {
            var sql = "UPDATE jc_user SET [dept_id]=@dept_id WHERE [user_id] in (" + usrIds + ")";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_id", deptId));
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public UserEntity QueryEntityByLoginid(string user_loginid)
        {
            UserEntity userEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();

            parameterList.Add(new SqlParameter("@user_loginid", user_loginid));
            string sql = "select user_id,user_name,staff_id,user_ctime,user_loginid,user_psw,user_status,u.auth_id,spqx_id,user_img,user_lasttime,authority,user_znxdx,user_rldx from jc_user u,user_auth a  " +
                             " where u.auth_id=a.auth_id and user_loginid=@user_loginid ";


            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null == result) return null;

            userEntity = new UserEntity();
            userEntity.user_id = int.Parse(result[0][0]);
            userEntity.user_name = result[0][1];
            userEntity.staff_id = int.Parse(result[0][2]);
            userEntity.user_ctime = result[0][3];
            userEntity.user_loginid = result[0][4];
            userEntity.user_psw = result[0][5];
            userEntity.user_status = result[0][6];
            userEntity.auth_id = int.Parse(result[0][7]);
            userEntity.spqx_id = int.Parse(result[0][8]);
            userEntity.user_img = result[0][9];
            userEntity.user_lasttime = result[0][10];
            userEntity.authority = result[0][11];
            userEntity.user_znxdx = int.Parse(result[0][12]);
            userEntity.user_rldx = int.Parse(result[0][13]);
            return userEntity;
        }

        public UserEntity QueryEntityByUserId(String userId)
        {
            UserEntity userEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();

            parameterList.Add(new SqlParameter("@user_id", userId));
            string sql = "select user_id,user_name,staff_id,user_ctime,user_loginid,user_psw,user_status,u.auth_id,spqx_id,user_img,user_lasttime,authority,user_znxdx,user_rldx from jc_user u,user_auth a  " +
                             " where u.auth_id=a.auth_id and u.user_id=@user_id ";


            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null == result) return null;

            userEntity = new UserEntity();
            userEntity.user_id = int.Parse(result[0][0]);
            userEntity.user_name = result[0][1];
            userEntity.staff_id = int.Parse(result[0][2]);
            userEntity.user_ctime = result[0][3];
            userEntity.user_loginid = result[0][4];
            userEntity.user_psw = result[0][5];
            userEntity.user_status = result[0][6];
            userEntity.auth_id = int.Parse(result[0][7]);
            userEntity.spqx_id = int.Parse(result[0][8]);
            userEntity.user_img = result[0][9];
            userEntity.user_lasttime = result[0][10];
            userEntity.authority = result[0][11];
            userEntity.user_znxdx = int.Parse(result[0][12]);
            userEntity.user_rldx = int.Parse(result[0][13]);
            return userEntity;
        }

        public void UpdateLoginTime(String loginId, String loginTime)
        {
            String sql = "update jc_user set [user_lasttime]=@loginTime,[user_status]='1' where [user_id]=@loginId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@loginTime", loginTime));
            parameterList.Add(new SqlParameter("@loginId", loginId));
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public UserEntity QueryEntityByName(string user_name)
        {
            UserEntity userEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_name", user_name));
            string sql = "select user_id,user_name,staff_id,user_ctime,user_loginid,user_psw,user_status,u.auth_id,spqx_id,user_img,user_lasttime,authority,user_znxdx,user_rldx from jc_user u,user_auth a  " +
                            " where u.auth_id=a.auth_id and user_name=@user_name ";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (null == result) return null;

            userEntity = new UserEntity();
            userEntity.user_id = int.Parse(result[0][0]);
            userEntity.user_name = result[0][1];
            userEntity.staff_id = int.Parse(result[0][2]);
            userEntity.user_ctime = result[0][3];
            userEntity.user_loginid = result[0][4];
            userEntity.user_psw = result[0][5];
            userEntity.user_status = result[0][6];
            userEntity.auth_id = int.Parse(result[0][7]);
            userEntity.spqx_id = int.Parse(result[0][8]);
            userEntity.user_img = result[0][9];
            userEntity.user_lasttime = result[0][10];
            userEntity.authority = result[0][11];
            userEntity.user_znxdx = int.Parse(result[0][12]);
            userEntity.user_rldx = int.Parse(result[0][13]);
            return userEntity;
        }

        public String QueryUserAuth(String user_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            String sql = "select auth_name from user_auth au, jc_user us where us.auth_id=au.auth_id and us.user_id=@user_id";
            parameterList.Add(new SqlParameter("@user_id", user_id));
            var result = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return result;
        }


        public DataTable QueryUserByIds(String[] user_ids)
        {
            if (user_ids == null || user_ids.Length == 0) return null;
            string sql = " select user_id 用户编码,user_name 用户名称,users.staff_id 员工编码,user_ctime 创建时间,user_loginid 账号,user_psw 密码,  " +
                             " user_status 状态,user_lasttime 上次登陆时间,auth_id 权限编码,spqx_id 审批权限编码,user_img 头像, " +
                            "  duty_name 职位名称,dept_name 部门名称  " +
                            "  from jc_user users,jc_dept depts,jc_duty dutys ,jc_staff staffs " +
                            "  where staffs.dept_id=depts.dept_id and staffs.duty_id=dutys.duty_id and users.staff_id=staffs.staff_id and user_status=1   ";
            if (user_ids.Length > 0)
            {
                sql += " and users.user_id in (";
                for (int i = 0; i < user_ids.Length; i++)
                {
                    if (!string.IsNullOrEmpty(user_ids[i]))
                    {
                        sql += "'" + user_ids[i] + "',";
                    }
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += ") ";
            }
            var result = DBExecuteUtil.querySqlTable(sql);
            return result;
        }


        public DataTable QueryUserByDeptId(string dept_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_id", dept_id));
            string sql = " select user_id 用户编码,user_name 用户名称,users.staff_id 员工编码,user_ctime 创建时间,user_loginid 账号,user_psw 密码,  " +
                             " user_status 状态,user_lasttime 上次登陆时间,auth_id 权限编码,spqx_id 审批权限编码,user_img 头像, " +
                            "  duty_name 职位名称,dept_name 部门名称,staff_kybz 在职标志  " +
                            "  from jc_user users,jc_dept depts,jc_duty dutys ,jc_staff staffs " +
                            "  where staffs.dept_id=depts.dept_id and staffs.duty_id=dutys.duty_id and users.staff_id=staffs.staff_id and staffs.dept_id=@dept_id  ";//and user_status=1

            DataTable dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public void UpdateEntity(UserEntity userEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", userEntity.user_id));
            parameterList.Add(new SqlParameter("@user_name", userEntity.user_name));
            parameterList.Add(new SqlParameter("@staff_id", userEntity.staff_id));
            parameterList.Add(new SqlParameter("@user_ctime", userEntity.user_ctime));
            parameterList.Add(new SqlParameter("@user_loginid", userEntity.user_loginid));
            parameterList.Add(new SqlParameter("@user_psw", userEntity.user_psw));
            parameterList.Add(new SqlParameter("@user_status", userEntity.user_status));
            parameterList.Add(new SqlParameter("@user_lasttime", userEntity.user_lasttime));
            parameterList.Add(new SqlParameter("@auth_id", userEntity.auth_id));
            parameterList.Add(new SqlParameter("@spqx_id", userEntity.spqx_id));
            parameterList.Add(new SqlParameter("@user_img", userEntity.user_img));
            parameterList.Add(new SqlParameter("@user_znxdx", userEntity.user_znxdx));
            parameterList.Add(new SqlParameter("@user_rldx", userEntity.user_rldx));

            string sql = "update jc_user set user_name=@user_name,staff_id=@staff_id,user_ctime=@user_ctime,user_loginid=@user_loginid,user_psw=@user_psw," +
                " user_status=@user_status,user_lasttime=@user_lasttime,auth_id=@auth_id, spqx_id=@spqx_id,user_img=@user_img,user_znxdx=@user_znxdx,user_rldx=@user_rldx where user_id=@user_id ";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }




        #region auth

        public void ChangeAuth(String userId, String authId)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            String sql = "update jc_user set auth_id=@authId where user_id=@userId";
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@authId", authId));
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        /// <summary>
        ///  添加类型
        /// </summary>
        /// <param name="authName"></param>
        /// <param name="auth_id"></param>
        public void AddAuth(String authName, String auth_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            String sql = "insert into user_auth ([auth_name],[auth_id]) values (@authName,@auth_id)";
            parameterList.Add(new SqlParameter("@authName", authName));
            parameterList.Add(new SqlParameter("@auth_id", auth_id));
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public DataTable QueryAllAuth()
        {
            String sql = "select auth_id,auth_name,authority from user_auth";
            return DBExecuteUtil.querySqlTable(sql);
        }



        public String GetAutority(String authId)
        {
            String sql = "select [authority] from user_auth where auth_id=@authId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@authId", authId));

            var auth_id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return auth_id;
        }

        public void AuthUpdate(string authId, string authority)
        {
            String sql = "update user_auth set authority='" + authority + "' where auth_id= " + authId;
            DBExecuteUtil.executeSql(sql);
        }
        #endregion

        public DataTable QueryMenuList()
        {
            String sql = "select [menu_id],[menu_supid],[menu_name] from system_menu order by [menu_supid], [menu_id]";
            var dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }

        #region tmp_user

        public void AddTempUser(string tmp_usr)
        {
            var user_info = tmp_usr.Split(',');

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@name", user_info[0]));
            parameterList.Add(new SqlParameter("dept", user_info[1]));
            parameterList.Add(new SqlParameter("duty", user_info[2]));
            parameterList.Add(new SqlParameter("gender", user_info[3]));
            parameterList.Add(new SqlParameter("birthday", user_info[4]));
            parameterList.Add(new SqlParameter("height", user_info[5]));
            parameterList.Add(new SqlParameter("race", user_info[6]));
            parameterList.Add(new SqlParameter("politics", user_info[7]));
            parameterList.Add(new SqlParameter("marrige", user_info[8]));
            parameterList.Add(new SqlParameter("schooling", user_info[9]));
            parameterList.Add(new SqlParameter("major", user_info[10]));
            parameterList.Add(new SqlParameter("identityCard", user_info[11]));
            parameterList.Add(new SqlParameter("phone", user_info[12]));
            parameterList.Add(new SqlParameter("revealed", user_info[13]));
            parameterList.Add(new SqlParameter("address", user_info[14]));
            parameterList.Add(new SqlParameter("timeIn", user_info[15]));
            parameterList.Add(new SqlParameter("salaryTry", user_info[16]));
            parameterList.Add(new SqlParameter("salaryOfficial", user_info[17]));

            var sql = "INSERT INTO tmp_user (name,dept,duty,gender,birthday,height,race,politics,marrige,schooling,major," +
                "identityCard,phone,revealed,address,timeIn,salaryTry,salaryOfficial) values (@name,@dept,@duty,@gender,@birthday," +
                "@height,@race,@politics,@marrige,@schooling,@major,@identityCard,@phone,@revealed,@address,@timeIn,@salaryTry,@salaryOfficial)";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public DataTable GetTmpUsers()
        {
            var sql = "SELECT id as 编号, name as 名称, dept as 部门, duty as 职务, gender as 性别, birthday as 生日,height as 身高, race as 民族, politics as 政治面貌, marrige as 婚姻状况," +
                "schooling as 最高学历, major as 专业, identityCard as 身份证, phone as 电话号码, revealed as 户籍, address as 现住地址,timeIn as 入职时间, salaryTry as 试用期工资," +
                "salaryOfficial as 转正工资 FROM tmp_user";

            var result = DBExecuteUtil.querySqlTable(sql);
            return result;
        }

        public DataTable GetTempUser(String name)
        {
            var sql = "SELECT id as 编号, name as 名称, dept as 部门, duty as 职务,gender as 性别, birthday as 生日,height as 身高, race as 民族, politics as 政治面貌, marrige as 婚姻状况," +
                "schooling as 最高学历, major as 专业, identityCard as 身份证, phone as 电话号码, revealed as 户籍, address as 现住地址,timeIn as 入职时间, salaryTry as 试用期工资," +
                "salaryOfficial as 转正工资 FROM tmp_user WHERE [name] = @name";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@name", name));
            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return result;
        }


        #endregion


    }
}
